
#include "database.h"

/**
 * database() constructor opens the database and randomizes 
 * seed for generating random strings
 */
database::database()
{
    sqlite3_open("project.db", &db);
    srand(time(NULL));
}

/**
 * database() destructor closes the opened database
 */
database::~database()
{
    sqlite3_close(db);
}

/**
 * write() sends a sqlite query to write to the database
 * @param query
 * @return
 */
int database::write(char *query)
{
    sqlite3_stmt *stmt = read(query);
    if (stmt)
    {
        sqlite3_finalize(stmt);
        return 1;
    }
    return 0;
}

/**
 * read() sends a sqlite query to read from the database
 * @param query
 * @return
 */
sqlite3_stmt *database::read(char *query)
{
    sqlite3_stmt *stmt;
    if (!sqlite3_prepare(db, query, strlen(query) + 1, &stmt, NULL))
    {
        sqlite3_step(stmt);
        return stmt;
    }
    return NULL;
}

/**
 * account_create() sends a sqlite query to create a new account in the database
 * @param username
 * @param password
 * @param access
 * @return
 */
int database::account_create(char *username, char *password, int access)
{
    char query[256];
    sprintf(query, "INSERT INTO accounts ('username','password','access') VALUES ('%s','%s','%d')", username, password, access);
    write(query);
    return 1;
}

/**
 * account_find() finds if a matching username and password is in the database for login purposes
 *
 * @param username
 * @param password
 * @return
 */
int database::account_find(char *username, char *password)
{
    char query[256];
    sprintf(query, "SELECT access FROM accounts WHERE username='%s' AND password='%s'", username, password);
    sqlite3_stmt *stmt = read(query);
    int access = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    if (!access)
    {
        sprintf(query, "SELECT rowid FROM reference WHERE refuser='%s' AND refpass='%s'", username, password);
        stmt = read(query);
        if (sqlite3_column_int(stmt, 0))
            access = A_REFERENCE;
        sqlite3_finalize(stmt);
    }
    return access;
}

/**
 * table_row_count() finds the number of rows in a specific database table
 * @param tablename
 * @return
 */
int database::table_row_count(const char *tablename)
{
    char query[256];
    sprintf(query, "SELECT count(rowid) FROM %s", tablename);
    sqlite3_stmt *stmt = read(query);
    int count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    return count;
}

int database::table_row_count(const char *tablename, const char *where, const char *what)
{
    char query[256];
    sprintf(query, "SELECT count(rowid) FROM %s WHERE %s='%s'", tablename, where, what);
    sqlite3_stmt *stmt = read(query);
    int count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    return count;
}

int database::table_row_count(const char *tablename, const char *where, const int what)
{
    char query[256];
    sprintf(query, "SELECT count(rowid) FROM %s WHERE %s='%d'", tablename, where, what);
    sqlite3_stmt *stmt = read(query);
    int count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    return count;
}

/**
 * table_row_max() fins the maximum ID of a row in a specific table
 * @param tablename
 * @return
 */
int database::table_row_max(const char *tablename)
{
    char query[256];
    sprintf(query, "SELECT max(rowid) FROM %s", tablename);
    sqlite3_stmt *stmt = read(query);
    int count = sqlite3_column_int(stmt, 0);
    sqlite3_finalize(stmt);
    return count;
}

/**
 * get_average_rating() gets the ratings of all reviewers for an app
 * @param appid
 * @return average rating
 */
int database::get_average_rating(int appid)
{
    int num = table_row_count("reviews", "appid", appid);
    if (!num)
        return 0;
    
    char query[256];
    sprintf(query, "SELECT rating FROM reviews WHERE appid='%d'", appid);
    sqlite3_stmt *stmt = read(query);

    int avgrating = 0;

    for (int i = 0; i != num; i++)
    {
        avgrating += sqlite3_column_int(stmt, 0);
        sqlite3_step(stmt);
    }

    sqlite3_finalize(stmt);

    return avgrating / num;
}

/**
 * generate_string() generates random strings of alphanumeric characters for
 * reference username and password
 * @param str
 */
void database::generate_string(char *str)
{
    int length = rand() % 6 + 8;
    int i = 0;
    while (i != length)
    {
        int ran = rand() % 75 + 48;
        if (isalnum(ran))
            str[i++] = (char)ran;
    }
}

/**
 * sendemail() sends an e-mail to the specified e-mail address
 * used for sending e-mails for specified references.
 * @param email
 * @param subject
 * @param message
 */
void database::sendemail(char *email, char *subject, char *message)
{
    char query[512];
    sprintf(query, "echo \"%s\"|mail -s \"%s\" %s", message, subject, email);
    system(query);
}

